/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package eyrolles.mymodele;

import eyrolles.Employe;
import eyrolles.modele.ModeleAccesIndisponibleException;
import eyrolles.modele.ModeleInterface;
import eyrolles.modele.ModeleUnknownUserException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import javax.sql.DataSource;

/**
 *
 * @author scriptoff
 */
public class ModeleImpl implements ModeleInterface {

    private DataSource dS = null;

    @Override
    public void setDataSource(DataSource dS) {
        this.dS = dS;
    }

    @Override
    public void updateUser(Employe emp)
            throws ModeleAccesIndisponibleException {

        String user = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
//    ServletContext context = null;


        try {

            /*Class.forName ("org.apache.derby.jdbc.ClientDriver");
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");
            stmt = conn.createStatement();*/


            try {
                conn = dS.getConnection();
                stmt = conn.createStatement();
            } catch (SQLException ex) {
                throw new ModeleAccesIndisponibleException(ex.getMessage());
            }
            StringBuffer sqlString =
                    new StringBuffer("update employes ");

            sqlString.append("set password='"
                    + emp.getPassword() + "', ");
            sqlString.append("roleid="
                    + emp.getRoleid() + ", ");
            sqlString.append("name='"
                    + emp.getName() + "', ");
            sqlString.append("phone='"
                    + emp.getPhone() + "', ");
            sqlString.append("email='"
                    + emp.getEmail() + "', ");
            sqlString.append("depid="
                    + emp.getDepid());
            sqlString.append(" where username='"
                    + emp.getUsername() + "'");
            try {
                stmt.execute(sqlString.toString());
            } catch (SQLException ex) {
                throw new ModeleAccesIndisponibleException(ex.getMessage());
            }
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
        }
    }

    @Override
    public void deleteEmploye(String username)
            throws ModeleAccesIndisponibleException {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
//        ServletContext context = null;


        try {

            /*Class.forName ("org.apache.derby.jdbc.ClientDriver");
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");
            stmt = conn.createStatement();*/
            try {
                conn = dS.getConnection();
                stmt = conn.createStatement();
            } catch (SQLException ex) {
                throw new ModeleAccesIndisponibleException(ex.getMessage());
            }

            StringBuffer sqlString =
                    new StringBuffer("delete from employes ");
            sqlString.append("where username='" + username + "'");
            try {
                stmt.execute(sqlString.toString());
            } catch (SQLException ex) {
                throw new ModeleAccesIndisponibleException(ex.getMessage());
            }
        } finally {

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
        }
    }

    @Override
    public ArrayList<Employe> getEmployes()
            throws ModeleAccesIndisponibleException {

        Employe employe = null;
        ArrayList<Employe> employes = new ArrayList<Employe>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {

            /*Class.forName ("org.apache.derby.jdbc.ClientDriver");
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");
            stmt = conn.createStatement();*/
            
            try {
                conn = dS.getConnection();
                stmt = conn.createStatement();
            } catch (SQLException ex) {
                throw new ModeleAccesIndisponibleException(ex.getMessage());
            }
            try {
                rs =
                        stmt.executeQuery("select * from employes, roles, "
                        + "services where employes.roleid=roles.roleid "
                        + "and employes.depid=services.depid");
            } catch (SQLException ex) {
                throw new ModeleAccesIndisponibleException(ex.getMessage());
            }

            while (rs.next()) {

                employe = new Employe();

                employe.setUsername(rs.getString("username"));
                employe.setName(rs.getString("name"));
                employe.setPhone(rs.getString("phone"));
                employe.setEmail(rs.getString("email"));
                employe.setRoleid(new Integer(rs.getInt("roleid")));
                employe.setDepid(new Integer(rs.getInt("depid")));

                employes.add(employe);

            }

        } catch (SQLException ex) {
            throw new ModeleAccesIndisponibleException(ex.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
                stmt = null;
            }
        }

        return employes;
    }

    public Employe buildEmploye(String username)
            throws ModeleAccesIndisponibleException, ModeleUnknownUserException {

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        Employe emp = null;
        //ServletContext context = null;

        try {

            /*Class.forName ("org.apache.derby.jdbc.ClientDriver");
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");*/
            conn = dS.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from employes where username=\'"
                    + username + "'");

            if (rs.next()) {

                emp = new Employe();


                emp.setUsername(rs.getString("username"));
                emp.setDepid(Integer.parseInt(rs.getString("depid")));
                emp.setRoleid(Integer.parseInt(rs.getString("roleid")));
                String name = rs.getString("name");
                //System.err.println("---->" + name + "<----");
                emp.setName(name);
                emp.setPhone(rs.getString("phone"));
                emp.setEmail(rs.getString("email"));
                emp.setPassword(rs.getString("password"));
            } else {
                throw new ModeleUnknownUserException(username);
            }
        } catch (SQLException ex) {
            throw new ModeleAccesIndisponibleException(ex.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
        }
        return emp;
    }

    @Override
    public String getUser(String username, String password)
            throws ModeleAccesIndisponibleException, ModeleUnknownUserException {

        String user = null;
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {

            /*Class.forName ("org.apache.derby.jdbc.ClientDriver");
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");
            stmt = conn.createStatement();*/
            conn = dS.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from employes where username=\'"
                    + username + "' "
                    + "and password='" + password + "'");

            if (rs.next()) {

                user = rs.getString("username");
                // Iteration sur le resultat
                System.err.println("Username : "
                        + user
                        + " Password : " + rs.getString("password"));
            } else {

                throw new ModeleUnknownUserException(username);
            }

        } catch (SQLException ex) {
            throw new ModeleAccesIndisponibleException(ex.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
                rs = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
                stmt = null;
            }
        }
        return user;
    }

    @Override
    public void insertUser(Employe emp) throws ModeleAccesIndisponibleException {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
//        ServletContext context = null;DBManager.freeConnection("cotations", conn);

        try {

            /*Class.forName ("org.apache.derby.jdbc.ClientDriver");
            conn = DriverManager.getConnection("jdbc:derby://localhost:1527/sample", "app", "app");
            stmt = conn.createStatement();*/
            conn = dS.getConnection();
            stmt = conn.createStatement();

            StringBuffer sqlString =
                    new StringBuffer("insert into employes ");

            sqlString.append("values ('"
                    + emp.getUsername() + "', ");
            sqlString.append("'"
                    + emp.getPassword() + "', ");
            sqlString.append("'"
                    + emp.getName() + "', ");
            sqlString.append(
                    emp.getRoleid() + ", ");
            sqlString.append("'"
                    + emp.getPhone() + "', ");
            sqlString.append("'"
                    + emp.getEmail() + "', ");
            sqlString.append(
                    emp.getDepid() + ")");

            stmt.execute(sqlString.toString());
        } catch (SQLException ex) {
            throw new ModeleAccesIndisponibleException(ex.getMessage());
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException ex) {
                    throw new ModeleAccesIndisponibleException(ex.getMessage());
                }
            }
        }
    }
}
